<?php

/* If no headers was sent: main call */
if (!headers_sent())
{
    $excel = True;

    require('database.php');
    require('auth.php');
    require('core.php');

    // Verifica se esta autenticado com pelo menos nivel 2
    validaPermissao(2);

    // Define file type
    header("Content-type: application/vnd.ms-excel");

    // Define file name and to be downloaded
    header("Content-Disposition: attachment; filename=\"Reports.xls\"");
}

/* Gets time period */
$from = (@$_GET['from']? strtotime($_GET['from']) : inicio_ultima_quinzena());
$to = (@$_GET['to']? strtotime($_GET['to']) : fim_ultima_quinzena());

// Mostra todos os reports
if (isset($_GET['customer']))
    $resultado = query_bd("SELECT reports.*, TIME_TO_SEC(reports.labor) AS seclabor FROM reports,events WHERE state = " . ACCEPTED .
    " AND reports.location_event_id=events.id AND " .
    "events.customer_id={$_GET['customer']} AND reports.special_event=TRUE" .
    (@$_GET['period'] != 0? " AND period={$_GET['period']}" : "") .
    ($from? " AND UNIX_TIMESTAMP(reports.reportdate) >= {$from}" : "") .
    ($to? " AND UNIX_TIMESTAMP(reports.reportdate) <= {$to}" : "") . " ORDER BY reportdate, period");
else
    $resultado = query_bd("SELECT *, TIME_TO_SEC(labor) AS seclabor FROM reports WHERE state = " . ACCEPTED .
    (isset($_GET['location'])? " AND location_event_id={$_GET['location']} AND special_event=FALSE" : "") .
    (@$_GET['period']? " AND period={$_GET['period']}" : "") .
    ($from? " AND UNIX_TIMESTAMP(reportdate) >= {$from}" : "") .
    ($to? " AND UNIX_TIMESTAMP(reportdate) <= {$to}" : "") . " ORDER BY reportdate, period");

$reports_num = mysql_affected_rows();

if (isset($_GET['location']))
    $location = query_fetch("SELECT contacts.name FROM locations, contacts " .
    "WHERE locations.id={$_GET['location']} AND contacts.id = locations.locationcontact");

if (isset($_GET['manager']))
    $manager = query_fetch("SELECT contacts.name FROM employees, contacts WHERE employees.id={$_GET['manager']}" .
            " AND contacts.id = employees.contact_id");

if (isset($_GET['customer']))
    $customer = query_fetch("SELECT contacts.name FROM customers, contacts WHERE customers.id={$_GET['customer']}" .
            " AND contacts.id = customers.contact_id");

// Imprime tabela
echo "<table id='reports'>";

if (!isset($excel))
    echo "<caption><em>{$reports_num} Reports ",
    ($from? " from " . date("M j Y", $from) : ""),
    ($to? " until " . date("M j Y", $to) : ""),
    (isset($_GET['location'])? " of <a href='location.php?id={$_GET['location']}'>{$location['name']}</a>" : ""),
    (isset($_GET['customer'])? " of <a href='customer.php?id={$_GET['customer']}'>{$customer['name']}</a>" : ""),
    (isset($_GET['manager'])? " managed by <a href='employee.php?id={$_GET['manager']}'>{$manager['name']}</a>" : ""),
    (@$_GET['period']? " at " . $PERIOD[$_GET['period']] : ""),
    "</em></caption>";

echo "<tr>", ((!isset($_GET['print']) && !isset($excel))? "<th>" : ""), "<th>Date",
    (!isset($_GET['location']) && !isset($_GET['customer'])? "<th>Location/Event" : ""),
    (!@$_GET['period']? "<th>Period" : ""),
    "<th>Labor<th>Labor<br>expense",
    (@$_GET['more']? "<th>Void<br>Validated<br>Hosted<th>Cars paid/charge" : ""),
    "<th>Total<br>cars<th>",
    (@$_GET['more']? "Charge<br>Insurance<br>Tip<br>Total" : "Total<br>income"),
    "<th>Balance<th>Income<br>per car<th>Expense<br>per car<th>Balance<br>per car";

unset($location, $manager, $customer);

$TOTAL_LABOR = 0;
$TOTAL_LABOR_EXP = 0.0;
$total_void = 0;
$total_validated = 0;
$total_hosted = 0;
$total_paid = 0;
$total_cars = 0;
$total_charge = 0.0;
$total_insurance = 0.0;
$total_tip = 0.0;
$TOTAL_INCOME = 0.0;
$CARS_CHARGE = array();

while ($report = mysql_fetch_array($resultado))
{
    flush();	/* Manda o que ja tem em buffer para o cliente */
    echo "<tr>";

    if (!isset($_GET['print']) && !isset($excel))
        echo "<td><a href='report.php?id={$report['id']}'><img src='img/report.png' alt='View report details'></a>";

    echo "<td>", date("D, M d Y", strtotime($report['reportdate']));

    // Busca nome da locacao ou evento
    if (!isset($_GET['location']) && !isset($_GET['customer']))
    {
        if ($report['special_event'])
        {
            $evento = query_fetch("SELECT name FROM events WHERE id={$report['location_event_id']} LIMIT 1");

            echo "<td><a href='"  . dirname($_SERVER['HTTP_REFERER']) . "/report.php?id={$report['id']}'>{$evento['name']}</a>";
        }
        else
        {
            $locacao = query_fetch("SELECT contacts.name, locations.operating FROM locations, contacts" .
                " WHERE locations.id={$report['location_event_id']} AND contacts.id = locations.locationcontact LIMIT 1");

            echo "<td><a href='"  . dirname($_SERVER['HTTP_REFERER']) . "/location.php?id={$report['location_event_id']}'>{$locacao['name']}",
            ($locacao['operating']? "" : "(not operating)"), "</a>";
        }
    }

    if (!@$_GET['period'])
    {
        //Periodo
        echo "<td>{$PERIOD[$report['period']]}";
    }

    // Soma subtotal com o total
    $TOTAL_LABOR += $report['seclabor'];
    $TOTAL_LABOR_EXP += $report['labor_expense'];

    echo "<td>", formatTime($report['labor']), "<td>", show_money(-$report['labor_expense']);

    // Void, validated, hosted, paid
    if (@$_GET['more'])
    {
        echo "<td>", ($report['cars_void']? $report['cars_void'] : "-"),
        "<br>", ($report['cars_validated']? $report['cars_validated'] : "-"),
        "<br>", ($report['cars_hosted']? $report['cars_hosted'] : "-");
    }

    //soma
    $total_void += $report['cars_void'];
    $total_validated += $report['cars_validated'];
    $total_hosted += $report['cars_hosted'];

    $paid_result = query_bd("SELECT cars, charge FROM cars_paid WHERE report_id={$report['id']}");

    if (@$_GET['more'])
        echo "<td>";

    $total_cars_paid = 0;
    $cars_paid_income = 0.0;
    while ($cars_paid = mysql_fetch_array($paid_result))
    {
        $CARS_CHARGE[$cars_paid['charge']] = (isset($CARS_CHARGE[$cars_paid['charge']])? $CARS_CHARGE[$cars_paid['charge']] : 0) + $cars_paid['cars'];
        if (@$_GET['more'])
            echo "{$cars_paid['cars']} at " . show_money($cars_paid['charge']) . "<br>";
        $total_cars_paid += $cars_paid['cars'];
        $cars_paid_income += $cars_paid['cars'] * $cars_paid['charge'];

        unset($cars_paid);
    }
    unset($paid_result);
    if (@$_GET['more'])
        echo "<hr>" . ($total_cars_paid? number_format($total_cars_paid) : ZERO);

    // Cars
    $cars = $report['cars_void'] + $report['cars_validated'] + $report['cars_hosted'] + $total_cars_paid;
    echo "<td>" . ($cars? number_format($cars) : ZERO);

    //soma
    $total_cars += $cars;
    $total_paid += $total_cars_paid;
    $total_charge += $cars_paid_income;

    // Charge income, tip income, insurance income
    $insurance_income = $cars * $report['insurance_tax'];
    $tip_income = ($report['tip']-$insurance_income);
    $tip_income = max(0, $tip_income);

    if (@$_GET['more'])
    {
        echo "<td>", show_money($cars_paid_income),
        "<br>", show_money($insurance_income),
        "<br>", show_money($tip_income), "<hr>";
    }
    else
        echo "<td>";

    //soma
    $total_tip += $tip_income;
    $total_insurance += $insurance_income;

    // Total income
    $total_income = $cars_paid_income + $tip_income;
    echo show_money($total_income);

    //soma
    $TOTAL_INCOME += $total_income;

    // Balance
    $balance = $cars_paid_income - $report['labor_expense'];
    echo "<td>", show_money($balance);

    //soma
//		$total_balance += $balance;

    // Income per car
    $income_car = ($cars > 0? $total_income/$cars : 0);
		echo "<td>", show_money($income_car);

		// Expense per car
		$expense_car = ($cars > 0? $report['labor_expense']/$cars : 0);
		echo "<td>", show_money(-$expense_car);

		// Balance per car
		echo "<td>", show_money($income_car - $expense_car);

		unset($report);
}
unset($resultado);

//tira conclusoes gerais
$total_balance = $TOTAL_INCOME - $TOTAL_LABOR_EXP;
$average_inc_car = ($total_cars> 0? $TOTAL_INCOME / $total_cars : 0);
$average_exp_car = ($total_cars> 0? $TOTAL_LABOR_EXP / $total_cars : 0);

echo "<tr>" . (!isset($_GET['print'])? "<th>" : ""), "<th><th>",
    (!isset($_GET['location']) && !isset($_GET['customer'])? "<th>" : ""),
    (!@$_GET['period']? "<th>" : ""),
    toTime($TOTAL_LABOR) . "<th>",
    show_money(-$TOTAL_LABOR_EXP);

if (@$_GET['more'])
{
echo "<th>", ($total_void? number_format($total_void) : ZERO), 
     "<br>", ($total_validated?  number_format($total_validated) : ZERO), 
     "<br>", ($total_hosted?  number_format($total_hosted) : ZERO) .
     "<th>";
}

if (@$_GET['more'])
{
    foreach ($CARS_CHARGE as $charge => $cars)
        echo "{$cars} at " . show_money($charge), "<br>";
    echo "<hr>", ($total_paid? $total_paid : ZERO);
}

echo "<th>", ($total_cars? number_format($total_cars) : ZERO), "<th>";

if (@$_GET['more'])
    echo show_money($total_charge), "<br>", show_money($total_insurance) .
    "<br>", show_money($total_tip), "<hr>";

echo show_money($TOTAL_INCOME), "<th>", show_money($total_balance), "<th>",
    show_money($average_inc_car), "<th>", show_money(-$average_exp_car), "<th>",
    show_money($average_inc_car - $average_exp_car);
echo "</table>";

?>
